﻿CREATE TABLE [dbo].[Entities] (
    [EntityID]     INT            IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL,
    [Name]         NVARCHAR (255) NOT NULL,
    [CreationDate] DATETIME       CONSTRAINT [DF_Entities_CreationDate] DEFAULT (getdate()) NOT NULL,
    [State]        SMALLINT       NOT NULL,
    [MID]          CHAR (10)      NULL,
    [EntityType]   INT            CONSTRAINT [DF_Entities_EntityType] DEFAULT ((1)) NOT NULL,
    CONSTRAINT [PK_Entities] PRIMARY KEY CLUSTERED ([EntityID] ASC),
    CONSTRAINT [FK_Entities_EntityTypes] FOREIGN KEY ([EntityType]) REFERENCES [dbo].[EntityTypes] ([ID]),
    CONSTRAINT [FK_Entities_States] FOREIGN KEY ([State]) REFERENCES [dbo].[States] ([ID])
);


GO
CREATE UNIQUE NONCLUSTERED INDEX [ix_MID]
    ON [dbo].[Entities]([MID] ASC)
    INCLUDE([EntityID]);


GO
CREATE NONCLUSTERED INDEX [ix_entityType]
    ON [dbo].[Entities]([EntityType] ASC);


GO
CREATE NONCLUSTERED INDEX [ix_state]
    ON [dbo].[Entities]([State] ASC)
    INCLUDE([EntityID]);


GO
CREATE STATISTICS [stat_mid]
    ON [dbo].[Entities]([MID]);


GO
CREATE STATISTICS [stat_state]
    ON [dbo].[Entities]([State]);

